﻿<cfsilent>
	<cfscript>
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		propAdvice = getProperty("serviceFactory").getBean("schemaPropertyAdvice");
		
		subjectId = event.getArg("SubID");
		grade = event.getArg("Edition");
		
		sql = "	SELECT
					sbj.sbj_id,
					sbj.sbj_name,
					sbj.sbj_short
				FROM
					t_subject sbj
				WHERE
					sbj.sbj_id = :subjectId ";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "subjectId", value = subjectId, cfsqltype = "cf_sql_varchar");
		rs_subjectEntity = queryObj.execute(sql = sql).getResult();

		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat( rs_subjectEntity.sbj_name & grade & "版" & "培养方案", "utf-8");
		
		hasAssistCourse = false;
		hasSecondDegree = false;

		spreadsheetObj = excel.SpreadSheetNew( rs_subjectEntity.sbj_short & grade & "版", false);
		excel.SpreadsheetAddrow(spreadsheetObj, "开课学期,课程类别,专业,年级,学位课,是否必修,课程号,课程,学分,任课单位,理论学时,实践学时,上机学时");
 
		/* 专业培养方案 */
		sql = "	SELECT
					s.term_index,
					cn.cna_name,
					sbj.sbj_name,
					cp.prop_name,
					c.course_code,
					c.course_name,
					c.course_credit,
					i.institute_name,
					c.period_theory,
					c.period_practice,
					c.period_computer,
					s.shm_prop
				FROM
					t_schema s
					INNER JOIN t_course_nature cn ON cn.cna_id = s.cna_id
					INNER JOIN t_subject sbj ON s.sbj_id = sbj.sbj_id
					INNER JOIN t_course_prop cp ON s.prop_id = cp.prop_id
					INNER JOIN t_course c ON s.cid = c.cid
					INNER JOIN t_institute i ON i.institute_id = c.institute_id
				WHERE
					s.grade = :grade 
					AND 
					(
						s.sbj_id LIKE :subjectId 
						OR
						s.sbj_id LIKE :subjectDir
					)
				ORDER BY
					s.term_index,
					sbj.sbj_id,
					cn.cna_name,
					cp.prop_name,
					c.course_name";
		
		queryObj = new Query(datasource = application.dnsSlave);
		queryObj.addParam(name = "subjectId", value = subjectId, cfsqltype = "cf_sql_varchar");
		queryObj.addParam(name = "grade", value = grade, cfsqltype = "cf_sql_char");
		queryObj.addParam(name = "subjectDir", value = subjectId & "_", cfsqltype = "cf_sql_varchar");
		rs_schema = queryObj.execute(sql = sql).getResult();
		
		for(a = 1; a lte rs_schema.recordCount; a++){
			
			propAdvice.parseProperty( rs_schema['shm_prop'][a] );
			/* 如果有辅修课程加入辅修课程选项卡 */
			if(propAdvice.getAssistCourse()){
				hasAssistCourse = true;
			}
			/* 如果有双学位课程加入双学位课程选项卡 */
			if(propAdvice.getSecondDegree()){
				hasSecondDegree = true;
			}
			rowData = rs_schema['term_index'][a] 
						& "," 
						& rs_schema['cna_name'][a]
						& "," 
						& rs_schema['sbj_name'][a]
						& "," 
						& grade;
			
			if ( propAdvice.getDegreeCourse() ) {
			
				rowData = rowData 
								& "," 
								& "是";
			}
			else {
				
				rowData = rowData 
								& "," 
								& " ";
				
			}
			
			rowData = rowData 
						& "," 
						& rs_schema['prop_name'][a]
						& ","
						& rs_schema['course_code'][a]
						& ","
						& rs_schema['course_name'][a]
						& ","
						& numberFormat( rs_schema['course_credit'][a], "_.__" )
						& ","
						& rs_schema['institute_name'][a];

			if ( rs_schema['period_theory'][a] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_theory'][a];
			}
			else {
				rowData = rowData & ", ";
			}
			
			if ( rs_schema['period_practice'][a] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_practice'][a];
			}
			else {
				rowData = rowData & ", ";
			}
			
			if ( rs_schema['period_computer'][a] gt 0 ) {
				rowData = rowData 
							& "," 
							& rs_schema['period_computer'][a];
			}
			else {
				rowData = rowData & ", ";
			}			
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
		}
		
		if( hasSecondDegree ) {

			/* 添加双学位课程 */
			excel.SpreadsheetCreateSheet (spreadsheetObj, rs_subjectEntity.sbj_short & grade & "版" & "双学位课程");		
			excel.SpreadsheetAddrow(spreadsheetObj, "开课学期,课程类别,专业,年级,是否必修,课程号,课程,学分,任课单位,理论学时,实践学时,上机学时");
		
			for(a = 1; a lte rs_schema.recordCount; a++){
			
				propAdvice.parseProperty( rs_schema['shm_prop'][a] );
			
				if ( propAdvice.getSecondDegree() ) {
			
					rowData = rs_schema['term_index'][a] 
								& "," 
								& rs_schema['cna_name'][a]
								& "," 
								& rs_schema['sbj_name'][a]
								& "," 
								& grade
								& "," 
								& rs_schema['prop_name'][a]
								& ","
								& rs_schema['course_code'][a]
								& ","
								& rs_schema['course_name'][a]
								& ","
								& numberFormat( rs_schema['course_credit'][a], "_.__" )
								& ","
								& rs_schema['institute_name'][a];

					if ( rs_schema['period_theory'][a] gt 0 ) {
						rowData = rowData 
									& "," 
									& rs_schema['period_theory'][a];
					}
					else {
						rowData = rowData & ", ";
					}
			
					if ( rs_schema['period_practice'][a] gt 0 ) {
						rowData = rowData 
									& "," 
									& rs_schema['period_practice'][a];
					}
					else {
						rowData = rowData & ", ";
					}
			
					if ( rs_schema['period_computer'][a] gt 0 ) {
						rowData = rowData 
									& "," 
									& rs_schema['period_computer'][a];
					}
					else {
						rowData = rowData & ", ";
					}
									
					excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				}
			}
		}
	
		if( hasAssistCourse ) { 
		
			/* 添加辅修课程 */
			excel.SpreadsheetCreateSheet (spreadsheetObj, rs_subjectEntity.sbj_short & grade & "版" & "辅修课程");		
			excel.SpreadsheetAddrow(spreadsheetObj, "开课学期,课程类别,专业,年级,是否必修,课程号,课程,学分,任课单位,理论学时,实践学时,上机学时");
		
			for(a = 1; a lte rs_schema.recordCount; a++){
			
				propAdvice.parseProperty( rs_schema['shm_prop'][a] );
			
				if ( propAdvice.getAssistCourse() ) {
				
					rowData = rs_schema['term_index'][a] 
								& "," 
								& rs_schema['cna_name'][a]
								& "," 
								& rs_schema['sbj_name'][a]
								& "," 
								& grade
								& "," 
								& rs_schema['prop_name'][a]
								& ","
								& rs_schema['course_code'][a]
								& ","
								& rs_schema['course_name'][a]
								& ","
								& numberFormat( rs_schema['course_credit'][a], "_.__" )
								& ","
								& rs_schema['institute_name'][a];

					if ( rs_schema['period_theory'][a] gt 0 ) {
						rowData = rowData 
									& "," 
									& rs_schema['period_theory'][a];
					}
					else {
						rowData = rowData & ", ";
					}
			
					if ( rs_schema['period_practice'][a] gt 0 ) {
						rowData = rowData 
									& "," 
									& rs_schema['period_practice'][a];
					}
					else {
						rowData = rowData & ", ";
					}
			
					if ( rs_schema['period_computer'][a] gt 0 ) {
						rowData = rowData 
									& "," 
									& rs_schema['period_computer'][a];
					}
					else {
						rowData = rowData & ", ";
					}
								
					excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				}
			}
		}
		
		excel.SpreadsheetSetActiveSheet (spreadsheetObj, rs_subjectEntity.sbj_short & grade & "版");
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />	

</cfsilent>